package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryMonthBeginDao;
import com.boss.salary.entity.SalaryMonthEnd;
import com.boss.salary.vo.SalaryMonthEndVO;
import com.boss.sys.utils.SysUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

@Repository
public class SalaryMonthBeginDaoImpl extends BaseDaoImpl implements ISalaryMonthBeginDao {

    @Override
    public void batchSaveSalaryMonthBegin(List<SalaryMonthEndVO> salaryMonthEndVOList) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        List<Object[]> paramList = new ArrayList<>(salaryMonthEndVOList.size());
        Class<?> clazz = SalaryMonthEndVO.class;
        for(SalaryMonthEndVO salaryMonthEndVO : salaryMonthEndVOList){
            Object[] paramArr = {
                    SysUtils.generateGuid(), String.valueOf(DateUtils.getCurrentTimeStamp()), String.valueOf(DateUtils.getCurrentTimeStamp()),
                    salaryMonthEndVO.getAgencyId(), salaryMonthEndVO.getPersonId(), salaryMonthEndVO.getMofDivCode()
            };
            List<Object> params = new ArrayList<>(Arrays.asList(paramArr));
            for(int i = 1; i < 101; i++){
                Method getMethod = clazz.getDeclaredMethod("getS"+i);
                params.add(getMethod.invoke(salaryMonthEndVO));
            }
            paramList.add(params.toArray());
        }
        StringBuilder sb = new StringBuilder();
        sb.append(" MERGE into ").append(TableName.SALARY_MONTH_BEGIN);
        sb.append(" t using (select ? s.id,? s.create_time,? s.update_time  ,?  s.agency_id  , ? s.person_id  , ? s.mof_div_code  ,   ")
                .append(" ? s.s1  , ? s.s2  , ? s.s3   ,?  s.s4   , ? s.s5  , ? s.s6   ,  ")
                .append(" ? s.s7,  ? s.s8 , ? s.s9 , ? s.s10, ? s.s11, ? s.s12, ? s.s13, ? s.s14, ? s.s15, ? s.s16,? s.s17,?  s.s18,? s.s19,? s.s20, ")
                .append(" ? s.s21 , ? s.s22 , ? s.s23 ,?  s.s24  , ? s.s25, ? s.s26 , ? s.s27,  ")
                .append(" ? s.s28, ? s.s29, ? s.s30, ? s.s31, ? s.s32, ? s.s33, ? s.s34, ? s.s35, ? s.s36,? s.s37,?  s.s38,? s.s39,? s.s40,    ")
                .append(" ? s.s41 , ? s.s42   , ? s.s43   ,?  s.s44  , ? s.s45  , ? s.s46  , ? s.s47, ? s.s48,  ")
                .append(" ? s.s49, ? s.s50, ? s.s51, ? s.s52, ? s.s53, ? s.s54, ? s.s55, ? s.s56,? s.s57,?  s.s58,? s.s59,? s.s60,    ")
                .append(" ? s.s61 , ? s.s62  , ? s.s63  ,?  s.s64  , ? s.s65 , ? s.s66  , ? s.s67, ? s.s68,  ")
                .append(" ? s.s69, ? s.s70, ? s.s71, ? s.s72, ? s.s73, ? s.s74, ? s.s75, ? s.s76,? s.s77,?  s.s78,? s.s79,? s.s80,   ")
                .append(" ? s.s81 , ? s.s82 , ? s.s83  ,?  s.s84  , ? s.s85   , ? s.s86 , ? s.s87, ? s.s88, ? s.s89, ")
                .append(" ? s.s90, ? s.s91, ? s.s92, ? s.s93, ? s.s94, ? s.s95, ? s.s96,? s.s97,?  s.s98,? s.s99,? s.s100 ) ")
                .append(" from sal_salary_month_begin ) s on (t.id=s.id)")
                .append(" when matched then update set " )
                .append(" ( t.id = s.id,t.create_time=s.create_time,t.update_time=s.update_time, t.agency_id=s.agency_id, t.person_id=s.person_id, ")
                .append(" t.mof_div_code=s.mof_div_code, t.s1 = s.s1,t.s2 =s.s2,t.s3= s.s3,t.s4  =s.s4,t.s5 =s.s5,s6 =s.s6,t.s7=s.s7,t.s8 =s.s8 ,t.s9 =s.s9 ,")
                .append(" t.s10=s.s10,t.s11=s.s11, t.s12=s.s12,t.s13=s.s13, t.s14=s.s14,t.s15=s.s15,t.s16=s.s16,t.s17=s.s17,t.s18=s.s18, t.s19=s.s19,t.s20 =s.s20 , ")
                .append(" t.s21= s.s21,t.s22=s.s22,t.s23 = s.s23,t.s24 =s.s24,t.s25=s.s25,s26=s.s26,t.s27=s.s27,t.s28=s.s28,t.s29=s.s29, ")
                .append(" t.s30=s.s30,t.s31=s.s31,t.s32=s.s32,t.s33=s.s33, t.s34=s.s34,t.s35=s.s35,t.s36=s.s36,t.s37=s.s37,t.s38=s.s38,t.s39=s.s39,t.s40 =s.s40,    ")
                .append(" t.s41= s.s41,t.s42=s.s42 ,t.s43 = s.s43,t.s44 =s.s44,t.s45=s.s45,t.s46=s.s46,t.s47=s.s47,t.s48=s.s48,t.s49=s.s49,")
                .append(" t.s50=s.s50,t.s51=s.s51,t.s52=s.s52,t.s53=s.s53,t.s54=s.s54,t.s55=s.s55, t.s56=s.s56,t.s57=s.s57, t.s58=s.s58, t.s59=s.s59, t.s60 =s.s60 ,")
                .append(" t.s61= s.s61, t.s62=s.s62,t.s63 = s.s63,t.s64 =s.s64,t.s65=s.s65,t.s66=s.s66,t.s67=s.s67,t.s68=s.s68,t.s69=s.s69, ")
                .append(" t.s70=s.s70,t.s71=s.s71,t.s72=s.s72,t.s73=s.s73,t.s74=s.s74,t.s75=s.s75,t.s76=s.s76,t.s77=s.s77, t.s78=s.s78,t.s79=s.s79,t.s80 =s.s80,    ")
                .append(" t.s81= s.s81 ,t.s82=s.s82,t.s83 = s.s83,t.s84 =s.s84,t.s85=s.s85,t.s86=s.s86,t.s87=s.s87,t.s88=s.s88,t.s89=s.s89,t.s90=s.s90,  ")
                .append(" t.s91=s.s91,t.s92=s.s92,t.s93=s.s93,t.s94=s.s94,t.s95=s.s95,t.s96=s.s96,t.s97=s.s97, t.s98=s.s98, t.s99=s.s99, t.s100=s.s100)  ")
                .append(" when not matched then insert values " )
                .append(" ( s.id, s.create_time, s.update_time, s.agency_id, s.person_id, s.mof_div_code,")
                .append(" s.s1, s.s2 , s.s3, s.s4, s.s5s., s6, s.s7,  s8 ,")
                .append(" s.s9 , s.s10, s.s11, s.s12, s.s13, s.s14, s.s15, s.s16, s.s17, s.s18, s.s19, s.s20, ")
                .append(" s.s21 , s.s22, s.s23, s.s24 , s.s25 , s26, s.s27, s28,")
                .append(" s.s29, s.s30, s.s31, s.s32, s.s33, s.s34, s.s35, s.s36, s.s37, s.s38, s.s39, s.s40, ")
                .append(" s.s41 , s.s42, s.s43, s.s44 , s.s45, s.s46, s.s47, s48,")
                .append(" s.s49, s.s50, s.s51, s.s52, s.s53, s.s54, s.s55, s.s56, s.s57, s.s58, s.s59, s.s60, ")
                .append(" s.s61 , s.s62 , s.s63 , s.s64, s.s65 , s.s66 , s.s67, s.s68, ")
                .append(" s.s69, s.s70, s.s71, s.s72, s.s73, s.s74, s.s75, s.s76, s.s77, s.s78, s.s79, s.s80, ")
                .append(" s.s81 , s.s82, s.s83 , s.s84 , s.s85, s.s86, s.s87, s88,")
                .append(" s.s89, s.s90, s.s91, s.s92, s.s93, s.s94, s.s95, s.s96, s.s97, s.s98, s.s99, s.s100 )");


        super.getJdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramList.get(i);
                for(int a = 0; a < 106; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void batchPhysicalDeleteSalaryMonthBegin(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from ").append(TableName.SALARY_MONTH_BEGIN)
                .append(" where ").append(" mof_div_code = ? ");
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        }
        super.getjdbcTemplate().update(sb.toString(), province);
    }
    @Override
    public SalaryMonthEnd selectSalaryBeginByPersonId(String personId) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" id, create_time, update_time, agency_id, person_id,mof_div_code,")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, ")
                .append(" s39, s40, s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, ")
                .append(" s57, s58, s59, s60, s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, ")
                .append(" s75, s76, s77, s78, s79, s80, s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, ")
                .append(" s93, s94, s95, s96, s97, s98, s99, s100 ")
                .append(" from ")
                .append(TableName.SALARY_MONTH_BEGIN)
                .append(" where ").append(" person_id = ?");
        Object[] params = {
                personId
        };
        try {
            return super.getJdbcTemplate().queryForObject(sb.toString(), params, new BeanPropertyRowMapper<>(SalaryMonthEnd.class));
        } catch (EmptyResultDataAccessException e) {
            return null;
        }

    }

    @Override
    public List<SalaryMonthEndVO> selectSalaryMonthEndWithSalaryType(String province, List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" pet.agency_id, pet.person_id, pet.merit_pay_code, pet.mof_div_code, ")
                .append(" s1, s2, s3, s4, s5, s6, s7, s8, s9, s10, s11, s12, s13, s14, s15, s16, s17, s18, s19, s20, ")
                .append(" s21, s22, s23, s24, s25, s26, s27, s28, s29, s30, s31, s32, s33, s34, s35, s36, s37, s38, s39, s40, ")
                .append(" s41, s42, s43, s44, s45, s46, s47, s48, s49, s50, s51, s52, s53, s54, s55, s56, s57, s58, s59, s60, ")
                .append(" s61, s62, s63, s64, s65, s66, s67, s68, s69, s70, s71, s72, s73, s74, s75, s76, s77, s78, s79, s80, ")
                .append(" s81, s82, s83, s84, s85, s86, s87, s88, s89, s90, s91, s92, s93, s94, s95, s96, s97, s98, s99, s100 ")
                .append(" from ").append(TableName.SAL_PERSON_EXT_BEGIN).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_BEGIN).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(province);

            sb.append(" and ").append(SysUtils.inSql("smed.agency_id", agencyIds));

        try{
            return super.getJdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(SalaryMonthEndVO.class));
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }


    @Override
    public List<String> getIsNotEmptybeginAgenyIds(String province, List<String> agencyIds) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append(" select ")
                .append(" distinct smed.agency_id ")
                .append(" from ").append(TableName.SAL_PERSON_EXT_BEGIN).append(" pet ")
                .append(" join ").append(TableName.SALARY_MONTH_BEGIN).append(" smed ")
                .append(" on ").append(" pet.person_id = smed.person_id ")
                .append(" where ").append(" pet.is_deleted = 0 and smed.mof_div_code = ? ");
        paramList.add(province);
        sql.append(" and ").append(SysUtils.inSql("smed.agency_id", agencyIds));
        try{
            return this.getJdbcTemplate().queryForList(sql.toString(),String.class,paramList.toArray());
        }catch(EmptyResultDataAccessException e){
            return Collections.emptyList();
        }
    }
}
